package edu.colorado.karl.databases;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import edu.colorado.karl.login.UserRecord;

/**
 * Abstraction of the User Database.  UserDatabase functions as the bridge to 
 * the MySQL databases storing user profile information.
 * @author Addison LeMessurier
 * @version 5/4/08
 */
public class UserDatabase {
	//used for log4j logging
	private static final Log log = LogFactory.getLog(UserDatabase.class);
	
	/**
	 * Sole constructor.
	 */
	public UserDatabase() {
		
	}

	/**
	 * Add a user to the database and set their privileges.
	 * @param username	The name of the new user.
	 * @param isAdmin	<code>true</code> if the user should have
	 * administrative privileges, <code>false</code> otherwise.
	 */
	public void addUser(String username, boolean isAdmin)
	{
		Connection c = UserDatabaseConnector.getConnection();
		String update = "INSERT INTO user_data " +
				"VALUES ('" + username + "','8877cb00ea8877664433a16699f84477',";
		String check = "SELECT * FROM user_data;";
		ResultSet rs = null;
		String name = "";
		
		// if the user is an administrator the access level should be 2
		if (isAdmin) {
			update = update + "'2');";
		}
		// residential users have an access level of 1
		else {
			update = update + "'1');";
		}
		
		if(c != null)
		{
			try
			{	
				Statement s = c.createStatement();
				
				rs = s.executeQuery(check);
				while(rs.next())
				{
					name = rs.getString(1);
					if(name.equals(username))
					{
						log.warn("Username taken");
						return;
					}
				}
				
				s.executeUpdate(update);
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
			
		}
		else
		{
			log.warn("Cannot connect to user database.");
		}
		
		UserDatabaseConnector.closeConnection(c);
	}
	
	/**
	 * Retrieve a list of the users in the User Database.
	 * @return a LinkedList of users found in the database.
	 */
	public static LinkedList<String> getUsers()
	{
		LinkedList<String> users = new LinkedList<String>();
		Connection c = UserDatabaseConnector.getConnection();
		String query = "SELECT * from user_data;";
		
		if(c != null)
		{
			try
			{
				Statement s = c.createStatement();
				s.executeQuery(query);
				ResultSet rs = s.getResultSet();
				
				while(rs.next())
				{
					users.add(rs.getString(1));
				}
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
			
		}
		else
		{
			log.warn("Cannot connect to user database.");
		}
		
		UserDatabaseConnector.closeConnection(c);
		
		return users;
	}
	
	/**
	 * Grants the specified user administrative privileges.
	 * @param username	The user who will gain admin power.
	 */
	public void grantAdminAccess(String username)
	{
		Connection c = UserDatabaseConnector.getConnection();
		String update = "UPDATE user_data SET access_level = '2' WHERE username = '" + username + "';";
		if(c != null)
		{
			try
			{	
				Statement s = c.createStatement();
				s.executeUpdate(update);
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
		}
		
		UserDatabaseConnector.closeConnection(c);
	}
	
	/**
	 * Deletes a user account from the User Database.
	 * @param username	The name of the user to delete.
	 */
	public void removeUser(String username)
	{
		Connection c = UserDatabaseConnector.getConnection();
		String update = "DELETE FROM user_data WHERE username = '" + username + "';";
		
		if(c != null)
		{
			try
			{
				Statement s = c.createStatement();
				s.executeUpdate(update);
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
			
		}
		else
		{
			log.warn("Cannot connect to user database.");
		}
		
		UserDatabaseConnector.closeConnection(c);
	}
	
	/**
	 * Strips away administrative privileges from the specified user.
	 * @param username	The user to remove privileges from.
	 */
	public void revokeAdminAccess(String username)
	{
		Connection c = UserDatabaseConnector.getConnection();
		String update = "UPDATE user_data SET access_level = '1' WHERE username = '" + username + "';";
		if(c != null)
		{
			try
			{	
				Statement s = c.createStatement();
				s.executeUpdate(update);
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
		}
		
		UserDatabaseConnector.closeConnection(c);
	}
	
	/**
	 * Retrieves a user record from the database.
	 * @param username	The name of the user to get.
	 * @return the user's information.
	 */
	public static UserRecord getUserRecord(String username)
	{
		UserRecord rec = new UserRecord();
		Connection c = UserDatabaseConnector.getConnection();
		String query = "SELECT * FROM user_data";
		String currentName = "";
		
		if(c != null)
		{
			try
			{
				Statement s = c.createStatement();
				s.executeQuery(query);
				ResultSet rs = s.getResultSet();
				
				while(rs.next())
				{
					currentName = rs.getString(1);
					if(currentName.equals(username))
					{
						rec.setPasswordMD5(rs.getString(2));
						rec.setAccessLevel(Integer.parseInt(rs.getString(3)));
					}
				}
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
			
		}
		else
		{
			log.warn("Cannot connect to user database.");
		}
		
		UserDatabaseConnector.closeConnection(c);
		
		return rec;
	}
	
	/**
	 * Determines whether or not the given user is an administrator.
	 * @param username	The user to check.
	 * @return <code>true</code> if the user has administrative privileges.
	 */
	public static boolean userIsAdmin(String username)
	{
		Connection c = UserDatabaseConnector.getConnection();
		String query = "SELECT * FROM user_data";
		String currentName = "";
		int accessLevel = 0;
		
		if(c != null)
		{
			try
			{
				Statement s = c.createStatement();
				s.executeQuery(query);
				ResultSet rs = s.getResultSet();
				
				while(rs.next())
				{
					currentName = rs.getString(1);
					if(currentName.equals(username))
					{
						accessLevel = rs.getInt(3);
					}
				}
			}
			
			catch(SQLException e)
			{
				log.warn("UDB: SQL exception", e);
			}
			
		}
		else
		{
			log.warn("Cannot connect to user database.");
		}
		
		UserDatabaseConnector.closeConnection(c);
		
		return (accessLevel > 1);
	}
 }
